const Router = require('koa-router');
const tool = require("../../tools/tool");
const router = new Router();
const {db, QueryTypes} = require("../../models/db");
const tools = require("../../tools/tool");

// 应收账款汇总
router.post('/list/ysgp', async ctx => {
    try {
        let user = ctx.user
        let body = ctx.request.body
        let tenantId = user.tenantId;
        let buId = body.formData.buId;
        let salesId = body.formData.salesId;
        let displayZero = body.formData.displayZero;
        let beg = body.formData.beginDate ? body.formData.beginDate : '2001-01-01';
        let end = body.formData.endDate ? body.formData.endDate : tools.getDay()
        let bu = ''
        if(buId) bu = ` and a.buId=${buId} `
        let sales = ''

        let contact = `select tenantId, id buId, name buName from contact where tenantId=${tenantId} `
        if(salesId) {
            contact = `select a.tenantId,b.buId,a.name buName from contact a inner join contact_sales b on a.tenantId=b.tenantId and a.id=b.buId and b.salesId=${salesId} and a.tenantId=${tenantId} `
        }
        let lastSql = `
            select a.tenantId, a.buId, a.initM, a.ys, a.rp, a.jy, a.skYh,c.buName from (
                select a.tenantId,a.buId,a.jy initM,ys,rp,a.jy+isnull(ys,0)-isnull(rp,0)-isnull(b.skYh,0) jy,isnull(b.skYh,0) skYh from (
                              select a.tenantId,a.buId, sum(jy) jy
                              from v_invoice_dz_ys a
                              where a.tenantId = ${tenantId} ${bu} 
                  and billDate < '${beg}' and billType<>'YS2YS' group by a.tenantId,a.buId
                ) a left join 
                (
                    select a.tenantId,a.buId,sum(ys) ys,sum(isnull(rpAmount, 0)) rp,sum(isnull(a.skYh,0)) skYh 
                        from v_invoice_dz_ys a 
                        where a.tenantId = ${tenantId} ${bu} 
                          and billDate >= '${beg}' and billDate<='${end}' ${sales} and billType<>'YS2YS'
                        group by a.tenantId,a.buId 
                ) b on a.tenantId=b.tenantId and a.buId=b.buId 
        ) a
            inner join (${contact}) c on a.buId=c.buId and a.tenantId=c.tenantId 
        `
        if(!displayZero) {
            lastSql += ` where jy <> 0`
        }
        lastSql += '  order by jy desc'
        let list = await db.query(lastSql, {type: QueryTypes.SELECT})

        list.map(row => {
            if(tools.xe.toNumber(row.jy) === 0) row.jy = ''
        })

        return ctx.suc('', {
            total: 0,
            list: list
        })
    }
    catch (e) {

        return ctx.err(e);
    }
})


module.exports = router;